CoreData : PostgreSQL-9.1 Server Tuning
This page last changed on Jul 03, 2013 by mccann.
PostgreSQL-9.1 Server Tuning
Configuring a Postgres server to efficiently serve data is an involved exercise, but one that can be successful if a deliberate strategy is followed. This page exists to track setup and configuration changes and their results with the goal of making informed decisions on tuning a server for a particular application. We'll start with a STOQS database running on a VM configured according to WHD-16322:
Request_Submitted_By: mccann@mbari.org VM_Name: pgtest VM_Purpose: To test PostgeSQL tuning parameters for current and future geospatial databases VM_Expiration: 1 year VM_Support: NOT_IS_Supported Alt_Administrator: Mike McCann Project_Number: VM_OS: CentOS 6 - Recommended for Linux RAM: 4 Addl_RAM_Reason: Additional RAM needed to cache database requests and this needs to be closer to the production server value of 64 GB. VM_disk: 25 GB total space is fine, but I request 4 separate devices for mounting different "disks" for optimal database performance. Here is the requested layout: / (root) - Operating system [10 GB - or what you think is really needed] $PGDATA - Database data [10 GB] $PGDATA/pg_xlog - DB Write Ahead Log (WAL) [3 GB] Tablespace - Temporary files [2 GB] VM_Comments: I request root (or sudo to everything) on the system. The system really is temporary. Once the production PostgreSQL server is in place and configured (by September 2013) this server will no longer be needed,
6 May 2013
After the VM was setup the following was done:
- Installed PostgreSQL-9.1 and postgis via yum(1)
- Initialized cluster with "/sbin/service postgresql-9.1 initdb"
- Moved px_xlog, pg_tblspace, data to their own partitions
Current disk/directory layout on pgtest:
[root@pgtest 9.1]# mount -t ext4 /dev/mapper/vg_templatecentos64-lv_root on / type ext4 (rw) /dev/sda1 on /boot type ext4 (rw) /dev/sdb1 on /pgdata type ext4 (rw) /dev/sdc1 on /pg_xlog type ext4 (rw) /dev/sdd1 on /tablespace type ext4 (rw) [root@pgtest 9.1]# ls -l total 8 drwx------ 2 postgres postgres 4096 Apr 2 09:24 backups lrwxrwxrwx 1 root root 12 May 6 16:02 data -> /pgdata/data -rw------- 1 postgres postgres 1749 May 6 16:03 pgstartup.log [root@pgtest 9.1]# cd data [root@pgtest data]# ls -l total 84 drwx------ 6 postgres postgres 4096 May 6 16:05 base drwx------ 2 postgres postgres 4096 May 6 16:12 global drwx------ 2 postgres postgres 4096 May 6 15:29 pg_clog -rw------- 1 postgres postgres 4232 May 6 15:29 pg_hba.conf -rw------- 1 postgres postgres 1636 May 6 15:29 pg_ident.conf drwx------ 2 postgres postgres 4096 May 6 15:33 pg_log drwx------ 4 postgres postgres 4096 May 6 15:29 pg_multixact drwx------ 2 postgres postgres 4096 May 6 16:03 pg_notify drwx------ 2 postgres postgres 4096 May 6 15:29 pg_serial drwx------ 2 postgres postgres 4096 May 6 16:30 pg_stat_tmp drwx------ 2 postgres postgres 4096 May 6 15:29 pg_subtrans lrwxrwxrwx 1 root root 21 May 6 15:57 pg_tblspc -> /tablespace/pg_tblspc drwx------ 2 postgres postgres 4096 May 6 15:29 pg_twophase -rw------- 1 postgres postgres 4 May 6 15:29 PG_VERSION lrwxrwxrwx 1 root root 16 May 6 15:38 pg_xlog -> /pg_xlog/pg_xlog -rw------- 1 postgres postgres 19146 May 6 15:29 postgresql.conf -rw------- 1 postgres postgres 71 May 6 16:03 postmaster.opts -rw------- 1 postgres postgres 81 May 6 16:03 postmaster.pid
Starting on changing default system configurations (previous .conf file moved to <file>.YYMMDD file, copied to <file> and edited):
- /etc/sysctl.conf: vm.swappiness = 0 and vm.overcommit_memory = 2
- /etc/fstab: Set noatime on the database partitions
- /etc/rc.local: blockdev --setra 8192 /dev/sdb1 (pgdata)
7 May 2013
Postgresql & system firewall configuration changes to allow connections across the network (see diffs from .130507 conf files):
- Edited /var/lib/pgsql/9.1/data/pg_hba.conf
- Edited /var/lib/pgsql/9.1/data/postgresql.conf
- Executed "sudo python /usr/bin/system-config-firewall" and added 5432:tcp & 5432:udp
From machine odss-test:
- Added stoqs_september2012_pgtest to ~odssadm/dev/stoqshg/privateSettings pointing to the database on pgtest
- Added stoqsadm user and created stoqs_september2012 database according to instructions in the STOQS INSTALL file
- Started "~odssadm/dev/stoqshg/loaders/CANON/loadCANON_september2012.py 1 stoqs_september2012_pgtest" at 7 May 2013 1550 PDT
- Monitoring vmstat 1 and iostat 1 on pgtest
- vmstat showing no swap activity. Good.
- iostat showing ~215 tps and ~3500 Blk_wrtn/s to sdc, which is /pg_xlog every second
- iostat showing 64 to 500 Blk_wrtn/s every 3 seconds or so to sdb, which is /pgdata
8 May 2013
stoqs_march2013_s database copied to pgtest from odss-test by Neil using pg_dump / pg_restore to serve as a good test database that has 3,381,366 data values. Initial tests shows comparable performance between the databases accessible from the stoqs ui at http://odss-test.shore.mbari.org/canon/stoqs_march2013_s/query/ and http://odss-test.shore.mbari.org/canon/stoqs_march2013_s_pgtest/query/. Direct queries from psql on each machine are also comparable, e.g.:
On odss-test:
stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=629955.51..638407.16 rows=3380659 width=20) (actual time=6082.363..6971.959 rows=3381814 loops=1)
Sort Key: datavalue
Sort Method: external merge Disk: 112424kB
-> Seq Scan on stoqs_measuredparameter (cost=0.00..55347.59 rows=3380659 width=20) (actual time=0.020..390.344 rows=3381814 loops=1)
Total runtime: 7133.318 ms
(5 rows)
On pgtest:
stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=630174.15..638628.68 rows=3381814 width=20) (actual time=6214.177..7099.968 rows=3381814 loops=1)
Sort Key: datavalue
Sort Method: external merge Disk: 112424kB
-> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..398.572 rows=3381814 loops=1)
Total runtime: 7266.122 ms
(5 rows)
Changing shared memory from initial value of 32 MB to 2 GB (half of all physical memory)
First execute script from Page 101 in Postgres 9.0 High Performance book:
<154 pgtest.shore.mbari.org /u/mccann> cat ./pg_smmem.sh #!/bin/bash # simple shmsetup script page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo kernel.shmmax = $shmmax echo kernel.shmall = $shmall <155 pgtest.shore.mbari.org /u/mccann> ./pg_smmem.sh kernel.shmmax = 2009198592 kernel.shmall = 490527
Modified system and postgres .conf files:
- /etc/sysctl.conf with kernel values from above
- postgresql.conf with shared_buffers = 1 GB (I tried setting it to 2 GB, but got an error when restarting postgresql-9.1; our test db is only 778 MB anyway)
No joy for immediate increase in performance. After rebooting the "explain analyze select * from stoqs_measuredparameter order by datavalue;" query still takes about 7.5 seconds.
Installed postgres contrib package in order to get tools for peeking at Postgres's memory cache: "yum install postgresql91-contrib.x86_64". Continuing to read Chapter 5...
10 May 2013
Some work_mem setting tests:
stoqs_march2013_s=# show work_mem;
work_mem
----------
1MB
(1 row)
stoqs_march2013_s=# set work_mem='354MB';
SET
stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=7276.353..7659.802 rows=3381814 loops=1)
Sort Key: datavalue
Sort Method: external sort Disk: 112400kB
-> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.048..382.085 rows=3381814 loops=1)
Total runtime: 7828.067 ms
(5 rows)
stoqs_march2013_s=# set work_mem='355MB';
SET
stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2399.499..2833.767 rows=3381814 loops=1)
Sort Key: datavalue
Sort Method: quicksort Memory: 362509kB
-> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..357.892 rows=3381814 loops=1)
Total runtime: 2998.478 ms
(5 rows)
Bingo! Passing a threshold to 355MB of work_mem lets the sort happen as a quicksort in memory decreasing query time by better than half!
Suggestions at http://stackoverflow.com/questions/8106181/work-mem-in-postgresql-on-linux and
http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/ are that the application can temporarily set the work_mem
to an appropriate value for the queries that need more than the default value and then reset it when the query is done. Setting it
globally to a large value can cause unneeded use of work_mem leading to out of memory errors.
13 May 2013
Following up on 8 May 2013 note, finishing pg_buffercache install. Note: instructions on p. 105 are not correct for pgsql-9.1.
This completes the install and tests that it is installed:
postgres=# create extension pg_buffercache;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
----------------+---------+------------+---------------------------------
pg_buffercache | 1.0 | public | examine the shared buffer cache
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
postgres=# select name,setting,unit,current_setting(name) from pg_settings where name='shared_buffers';
name | setting | unit | current_setting
----------------+---------+------+-----------------
shared_buffers | 131072 | 8kB | 1GB
(1 row)
14 May 2013
Following one of the suggestions from the PERFORM list, it seems that setting random_page_cost to 1 (from the default of 4) helps improve an
actual query performed by the STOQS UI. With default random_page_cost = 4:
stoqs_march2013_s=# set random_page_cost=4;
SET
stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,
stoqs_parameter.name AS parameter__name,
stoqs_parameter.standard_name AS parameter__standard_name,
stoqs_measurement.depth AS measurement__depth,
stoqs_measurement.geom AS measurement__geom,
stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,
stoqs_platform.name AS measurement__instantpoint__activity__platform__name,
stoqs_measuredparameter.datavalue AS datavalue,
stoqs_parameter.units AS parameter__units
FROM stoqs_parameter p1,
stoqs_measuredparameter
INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)
INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)
INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)
INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)
INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id
WHERE (p1.name = 'sea_water_sigma_t')
AND (mp1.datavalue > 25.19)
AND (mp1.datavalue < 26.01)
AND (mp1.parameter_id = p1.id)
AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'
AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'
AND stoqs_parameter.name IN ('fl700_uncorr')
AND stoqs_measurement.depth >= -1.88
AND stoqs_platform.name IN ('dorado')
AND stoqs_measurement.depth <= 83.57)
ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------
Sort (cost=10741.41..10741.42 rows=1 width=1282) (actual time=358.452..358.452 rows=0 loops=1)
Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=3002.89..10741.40 rows=1 width=1282) (actual time=358.445..358.445 rows=0 loops=1)
Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)
-> Nested Loop (cost=2983.69..10722.19 rows=3 width=954) (actual time=358.269..358.269 rows=0 loops=1)
-> Nested Loop (cost=2983.69..9617.36 rows=191 width=946) (actual time=12.788..296.022 rows=20170 loops=1)
-> Hash Join (cost=2983.69..8499.07 rows=193 width=842) (actual time=12.778..223.137 rows=20170 loops=1)
Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
-> Nested Loop (cost=2982.38..8478.47 rows=4628 width=24) (actual time=12.752..188.772 rows=197746 loops=1)
-> Nested Loop (cost=2982.38..4862.37 rows=512 width=4) (actual time=12.733..25.939 rows=20170 loops=1)
-> Seq Scan on stoqs_parameter p1 (cost=0.00..1.30 rows=1 width=4) (actual time=0.001..0.007 rows=1 loops=1)
Filter: ((name)::text = 'sea_water_sigma_t'::text)
-> Bitmap Heap Scan on stoqs_measuredparameter mp1 (cost=2982.38..4854.40 rows=534 width=8) (actual time=12.724..21.223 rows=20170 loops
=1)
Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))
-> BitmapAnd (cost=2982.38..2982.38 rows=534 width=0) (actual time=12.326..12.326 rows=0 loops=1)
-> Bitmap Index Scan on stoqs_measuredparameter_datavalue (cost=0.00..259.54 rows=12292 width=0) (actual time=5.938..5.938 r
ows=23641 loops=1)
Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))
-> Bitmap Index Scan on stoqs_measuredparameter_parameter_id (cost=0.00..2719.38 rows=147035 width=0) (actual time=5.847..5.
847 rows=34750 loops=1)
Index Cond: (parameter_id = p1.id)
-> Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter (cost=0.00..6.98 rows=7 width=20) (actual time=0.003..0.
005 rows=10 loops=20170)
Index Cond: (measurement_id = mp1.measurement_id)
-> Hash (cost=1.30..1.30 rows=1 width=826) (actual time=0.010..0.010 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on stoqs_parameter (cost=0.00..1.30 rows=1 width=826) (actual time=0.004..0.008 rows=1 loops=1)
Filter: ((name)::text = 'fl700_uncorr'::text)
-> Index Scan using stoqs_measurement_pkey on stoqs_measurement (cost=0.00..5.78 rows=1 width=116) (actual time=0.003..0.003 rows=1 loops=20170)
Index Cond: (id = stoqs_measuredparameter.measurement_id)
Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))
-> Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint (cost=0.00..5.77 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=20170)
Index Cond: (id = stoqs_measurement.instantpoint_id)
Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone))
-> Hash (cost=18.82..18.82 rows=30 width=336) (actual time=0.165..0.165 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Hash Join (cost=1.09..18.82 rows=30 width=336) (actual time=0.073..0.162 rows=7 loops=1)
Hash Cond: (stoqs_activity.platform_id = stoqs_platform.id)
-> Seq Scan on stoqs_activity (cost=0.00..16.77 rows=177 width=66) (actual time=0.006..0.048 rows=177 loops=1)
-> Hash (cost=1.07..1.07 rows=1 width=278) (actual time=0.018..0.018 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on stoqs_platform (cost=0.00..1.07 rows=1 width=278) (actual time=0.012..0.015 rows=1 loops=1)
Filter: ((name)::text = 'dorado'::text)
Total runtime: 358.698 ms
(42 rows)
With random_page_cost = 1:
stoqs_march2013_s=# set random_page_cost=1;
SET
stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,
stoqs_parameter.name AS parameter__name,
stoqs_parameter.standard_name AS parameter__standard_name,
stoqs_measurement.depth AS measurement__depth,
stoqs_measurement.geom AS measurement__geom,
stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,
stoqs_platform.name AS measurement__instantpoint__activity__platform__name,
stoqs_measuredparameter.datavalue AS datavalue,
stoqs_parameter.units AS parameter__units
FROM stoqs_parameter p1,
stoqs_measuredparameter
INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)
INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)
INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)
INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)
INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id
WHERE (p1.name = 'sea_water_sigma_t')
AND (mp1.datavalue > 25.19)
AND (mp1.datavalue < 26.01)
AND (mp1.parameter_id = p1.id)
AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'
AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'
AND stoqs_parameter.name IN ('fl700_uncorr')
AND stoqs_measurement.depth >= -1.88
AND stoqs_platform.name IN ('dorado')
AND stoqs_measurement.depth <= 83.57)
ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
Sort (cost=3930.09..3930.09 rows=1 width=1282) (actual time=185.676..185.676 rows=0 loops=1)
Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=1678.86..3930.08 rows=1 width=1282) (actual time=185.656..185.656 rows=0 loops=1)
Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
-> Nested Loop (cost=1677.55..3928.72 rows=10 width=464) (actual time=185.626..185.626 rows=0 loops=1)
-> Hash Join (cost=1677.55..3926.60 rows=1 width=456) (actual time=185.625..185.625 rows=0 loops=1)
Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)
-> Nested Loop (cost=1667.82..3916.85 rows=7 width=128) (actual time=185.548..185.548 rows=0 loops=1)
-> Nested Loop (cost=1667.82..3075.30 rows=507 width=120) (actual time=12.112..111.871 rows=20170 loops=1)
-> Nested Loop (cost=1667.82..2211.81 rows=512 width=4) (actual time=12.098..26.814 rows=20170 loops=1)
-> Seq Scan on stoqs_parameter p1 (cost=0.00..1.30 rows=1 width=4) (actual time=0.003..0.011 rows=1 loops=1)
Filter: ((name)::text = 'sea_water_sigma_t'::text)
-> Bitmap Heap Scan on stoqs_measuredparameter mp1 (cost=1667.82..2203.83 rows=534 width=8) (actual time=12.088..20.804 rows=20170 loops
=1)
Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))
-> BitmapAnd (cost=1667.82..1667.82 rows=534 width=0) (actual time=11.707..11.707 rows=0 loops=1)
-> Bitmap Index Scan on stoqs_measuredparameter_datavalue (cost=0.00..157.26 rows=12292 width=0) (actual time=5.885..5.885 r
ows=23641 loops=1)
Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))
-> Bitmap Index Scan on stoqs_measuredparameter_parameter_id (cost=0.00..1507.11 rows=147035 width=0) (actual time=5.265..5.
265 rows=34750 loops=1)
Index Cond: (parameter_id = p1.id)
-> Index Scan using stoqs_measurement_pkey on stoqs_measurement (cost=0.00..1.67 rows=1 width=116) (actual time=0.003..0.003 rows=1 loops=2017
0)
Index Cond: (id = mp1.measurement_id)
Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))
-> Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint (cost=0.00..1.65 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=20170)
Index Cond: (id = stoqs_measurement.instantpoint_id)
Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone)
)
-> Hash (cost=9.35..9.35 rows=30 width=336) (actual time=0.071..0.071 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Nested Loop (cost=0.00..9.35 rows=30 width=336) (actual time=0.057..0.064 rows=7 loops=1)
-> Seq Scan on stoqs_platform (cost=0.00..1.07 rows=1 width=278) (actual time=0.004..0.006 rows=1 loops=1)
Filter: ((name)::text = 'dorado'::text)
-> Index Scan using stoqs_activity_platform_id on stoqs_activity (cost=0.00..7.83 rows=35 width=66) (actual time=0.049..0.052 rows=7 loops=1)
Index Cond: (platform_id = stoqs_platform.id)
-> Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter (cost=0.00..2.02 rows=7 width=20) (never executed)
Index Cond: (measurement_id = stoqs_measurement.id)
-> Hash (cost=1.30..1.30 rows=1 width=826) (actual time=0.023..0.023 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on stoqs_parameter (cost=0.00..1.30 rows=1 width=826) (actual time=0.016..0.020 rows=1 loops=1)
Filter: ((name)::text = 'fl700_uncorr'::text)
Total runtime: 185.850 ms
(40 rows)
Query went from 0.36 seconds to 0.18 seconds.
Based on this the random_page_cost setting in postgresql.conf was changed from 4 to 1.01 on pgtest. Comparing similar queries between
http://odss-test.shore.mbari.org/canon/stoqs_march2013_s_pgtest/query/ and http://odss-test.shore.mbari.org/canon/stoqs_march2013_s/query/
shows no appreciable improvement in performance on the pgtest database.
15 May 2013
Preparing for when the STOQS application will be setting work_mem for the queries that need it I have set up logging of temp files that
are created when there is not enough work_mem for sorts to be done in memory. The instructions are in the book and on this page:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
PostgreSQL 8.3 and later
In 8.3 you can use log_temp_files to figure out if sorts are using disk instead of fitting in memory. In earlier versions, you might
instead just monitor the size of them by looking at how much space is being used in the various $PGDATA/base/<db oid>/pgsql_tmp files.
You can see sorts to disk happen in EXPLAIN ANALYZE plans as well. For example, if you see a line like "Sort Method: external merge
Disk: 7526kB" in there, you'd know a work_mem of at least 8MB would really improve how fast that query executed, by sorting in RAM
instead of swapping to disk.
The change to the postgresql.conf file was made today with the previous version saved to postgresql.conf.130515
16 May 2013
Another post to the pgsql-perform mail list asking for opinions on a more conventional server configuration:
From: Mike McCann <mccann@mbari.org>
Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
Date: May 13, 2013 3:36:03 PM PDT
To: pgsql-performance@postgresql.org
Cc: Yuri Levinsky <yuril@celltick.com>, Scott Marlowe <scott.marlowe@gmail.com>, Julien Cigar <jcigar@ulb.ac.be>,
Arjen van der Meijden <acmmailing@tweakers.net>, Jeff Janes <jeff.janes@gmail.com>
On May 7, 2013, at 4:21 PM, Jeff Janes wrote:
On Thu, May 2, 2013 at 6:35 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, May 2, 2013 at 5:11 PM, Mike McCann <mccann@mbari.org> wrote:
> Hello,
>
> We are in the fortunate situation of having more money than time to help
> solve our PostgreSQL 9.1 performance problem.
>
> Our server hosts databases that are about 1 GB in size with the largest
> tables having order 10 million 20-byte indexed records. The data are loaded
> once and then read from a web app and other client programs. Some of the
> queries execute ORDER BY on the results. There are typically less than a
> dozen read-only concurrent connections to any one database.
I wouldn't count on this being a problem that can be fixed merely by throwing money at it.
How many rows does any one of these queries need to access and then ORDER BY?
...
>
> HP ProLiant DL360p Gen 8
> Dual Intel Xeon 2.4GHz 4-core E5-2609 CPUs
> 64GB RAM
> 2x146GB 15K SAS hard drives
> 3x200GB SATA SLC SSDs
> + the usual accessories (optical drive, rail kit, dual power supplies)
If your DB is 1G, and will grow to 10G then the IO shouldn't be any
problem, as the whole db should be cached in memory.
But it can take a surprisingly long time to get it cached in the first place, from a cold start.
If that is the problem, pg_prewarm could help.
Cheers,
Jeff
Thank you everyone for your suggestions.
It's clear that our current read performance was not limited by hardware. An 'explain analyze' for a sample query is:
stoqs_march2013_s=# show work_mem;
work_mem
----------
1MB
(1 row)
stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=541002.15..549456.68 rows=3381814 width=20) (actual time=6254.780..7244.074 rows=3381814 loops=1)
Sort Key: datavalue
Sort Method: external merge Disk: 112424kB
-> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..354.385 rows=3381814 loops=1)
Total runtime: 7425.854 ms
(5 rows)
Increasing work_mem to 355 MB improves the performance by a factor of 2:
stoqs_march2013_s=# set work_mem='355MB';
SET
stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2503.078..2937.130 rows=3381814 loops=1)
Sort Key: datavalue
Sort Method: quicksort Memory: 362509kB
-> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
Total runtime: 3094.601 ms
(5 rows)
I tried changing random_page_cost to from 4 to 1 and saw no change.
I'm wondering now what changes might get this query to run in less than one second. If all the data is in memory, then will faster CPU and memory be
the things that help?
We have an alternate (a bit more conventional) server configuration that we are considering:
HP ProLiant DL360p Gen 8
Dual Intel Xeon 3.3GHz 4-core E5-2643 CPUs
128GB PC3-12800 RAM
16x146GB 15K SAS hard drives
HP Smart Array P822/2GB FBWC controller + P420i w/ 2GB FBWC
+ the usual accessories (optical drive, rail kit, dual power supplies)
All suggestions welcomed!
-Mike
--
Mike McCann
Software Engineer
Monterey Bay Aquarium Research Institute
7700 Sandholdt Road
Moss Landing, CA 95039-9644
Voice: 831.775.1769 Fax: 831.775.1736 http://www.mbari.org
The responses:
From: Mike McCann <mccann@mbari.org>
Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
Date: May 13, 2013 4:58:17 PM PDT
To: Jeff Janes <jeff.janes@gmail.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>, Yuri Levinsky <yuril@celltick.com>, Scott Marlowe <scott.marlowe@gmail.com>,
Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net>
On May 13, 2013, at 4:24 PM, Jeff Janes wrote:
> On Mon, May 13, 2013 at 3:36 PM, Mike McCann <mccann@mbari.org> wrote:
>
> Increasing work_mem to 355 MB improves the performance by a factor of 2:
>
> stoqs_march2013_s=# set work_mem='355MB';
> SET
> stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2503.078..2937.130 rows=3381814 loops=1)
> Sort Key: datavalue
> Sort Method: quicksort Memory: 362509kB
> -> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
> Total runtime: 3094.601 ms
> (5 rows)
>
> I tried changing random_page_cost to from 4 to 1 and saw no change.
>
> I'm wondering now what changes might get this query to run in less than one second.
I think you are worrying about the wrong thing here. What is a web app going to do with 3,381,814 rows, once it obtains them? Your current testing
is not testing the time it takes to stream that data to the client, or for the client to do something meaningful with that data.
If you only plan to actually fetch a few dozen of those rows, then you probably need to incorporate that into your test, either by using a LIMIT, or
by using a mock-up of the actual application to do some timings.
Also, what is the type and collation of the column you are sorting on? non-'C' collations of text columns sort about 3 times slower than 'C' collation does.
If all the data is in memory, then will faster CPU and memory be the things that help?
Yes, those would help (it is not clear to me which of the two would help more), but I think you need to rethink your design of sending the entire database
table to the application server for each page-view.
Cheers,
Jeff
Hi Jeff,
The datavalue column is double precision:
stoqs_march2013_s=# \d+ stoqs_measuredparameter
Table "public.stoqs_measuredparameter"
Column | Type | Modifiers | Storage | Description
----------------+------------------+----------------------------------------------------------------------+---------+-------------
id | integer | not null default nextval('stoqs_measuredparameter_id_seq'::regclass) | plain |
measurement_id | integer | not null | plain |
parameter_id | integer | not null | plain |
datavalue | double precision | not null | plain |
Indexes:
"stoqs_measuredparameter_pkey" PRIMARY KEY, btree (id)
"stoqs_measuredparameter_measurement_id_parameter_id_key" UNIQUE CONSTRAINT, btree (measurement_id, parameter_id)
"stoqs_measuredparameter_datavalue" btree (datavalue)
"stoqs_measuredparameter_measurement_id" btree (measurement_id)
"stoqs_measuredparameter_parameter_id" btree (parameter_id)
Foreign-key constraints:
"stoqs_measuredparameter_measurement_id_fkey" FOREIGN KEY (measurement_id) REFERENCES stoqs_measurement(id) DEFERRABLE INITIALLY DEFERRED
"stoqs_measuredparameter_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES stoqs_parameter(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no
Thanks for the suggestion and advice to examine the web app performance. We've actually taken quite a few steps to optimize how the web app works. The example query
I provided is a simple worst-case one that we can use to help us decide on the proper hardware. An actual query performed by the web app is:
stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,
stoqs_march2013_s-# stoqs_parameter.name AS parameter__name,
stoqs_march2013_s-# stoqs_parameter.standard_name AS parameter__standard_name,
stoqs_march2013_s-# stoqs_measurement.depth AS measurement__depth,
stoqs_march2013_s-# stoqs_measurement.geom AS measurement__geom,
stoqs_march2013_s-# stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,
stoqs_march2013_s-# stoqs_platform.name AS measurement__instantpoint__activity__platform__name,
stoqs_march2013_s-# stoqs_measuredparameter.datavalue AS datavalue,
stoqs_march2013_s-# stoqs_parameter.units AS parameter__units
stoqs_march2013_s-# FROM stoqs_parameter p1,
stoqs_march2013_s-# stoqs_measuredparameter
stoqs_march2013_s-# INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)
stoqs_march2013_s-# INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)
stoqs_march2013_s-# INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
stoqs_march2013_s-# INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)
stoqs_march2013_s-# INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)
stoqs_march2013_s-# INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id
stoqs_march2013_s-# WHERE (p1.name = 'sea_water_sigma_t')
stoqs_march2013_s-# AND (mp1.datavalue > 25.19)
stoqs_march2013_s-# AND (mp1.datavalue < 26.01)
stoqs_march2013_s-# AND (mp1.parameter_id = p1.id)
stoqs_march2013_s-# AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'
stoqs_march2013_s(# AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'
stoqs_march2013_s(# AND stoqs_parameter.name IN ('fl700_uncorr')
stoqs_march2013_s(# AND stoqs_measurement.depth >= -1.88
stoqs_march2013_s(# AND stoqs_platform.name IN ('dorado')
stoqs_march2013_s(# AND stoqs_measurement.depth <= 83.57)
stoqs_march2013_s-# ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------
Sort (cost=10741.41..10741.42 rows=1 width=1282) (actual time=770.211..770.211 rows=0 loops=1)
Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=3002.89..10741.40 rows=1 width=1282) (actual time=770.200..770.200 rows=0 loops=1)
Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)
-> Nested Loop (cost=2983.69..10722.19 rows=3 width=954) (actual time=770.036..770.036 rows=0 loops=1)
-> Nested Loop (cost=2983.69..9617.36 rows=191 width=946) (actual time=91.369..680.072 rows=20170 loops=1)
-> Hash Join (cost=2983.69..8499.07 rows=193 width=842) (actual time=91.346..577.633 rows=20170 loops=1)
Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
-> Nested Loop (cost=2982.38..8478.47 rows=4628 width=24) (actual time=91.280..531.408 rows=197746 loops=1)
-> Nested Loop (cost=2982.38..4862.37 rows=512 width=4) (actual time=91.202..116.140 rows=20170 loops=1)
-> Seq Scan on stoqs_parameter p1 (cost=0.00..1.30 rows=1 width=4) (actual time=0.002..0.011 rows=1 loops=1)
Filter: ((name)::text = 'sea_water_sigma_t'::text)
-> Bitmap Heap Scan on stoqs_measuredparameter mp1 (cost=2982.38..4854.40 rows=534 width=8) (actual time=91.194..109.846 rows=20170 loop
s=1)
Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))
-> BitmapAnd (cost=2982.38..2982.38 rows=534 width=0) (actual time=90.794..90.794 rows=0 loops=1)
-> Bitmap Index Scan on stoqs_measuredparameter_datavalue (cost=0.00..259.54 rows=12292 width=0) (actual time=62.769..62.769
rows=23641 loops=1)
Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))
-> Bitmap Index Scan on stoqs_measuredparameter_parameter_id (cost=0.00..2719.38 rows=147035 width=0) (actual time=27.412..2
7.412 rows=34750 loops=1)
Index Cond: (parameter_id = p1.id)
-> Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter (cost=0.00..6.98 rows=7 width=20) (actual time=0.008..0.
017 rows=10 loops=20170)
Index Cond: (measurement_id = mp1.measurement_id)
-> Hash (cost=1.30..1.30 rows=1 width=826) (actual time=0.012..0.012 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on stoqs_parameter (cost=0.00..1.30 rows=1 width=826) (actual time=0.007..0.010 rows=1 loops=1)
Filter: ((name)::text = 'fl700_uncorr'::text)
-> Index Scan using stoqs_measurement_pkey on stoqs_measurement (cost=0.00..5.78 rows=1 width=116) (actual time=0.004..0.004 rows=1 loops=20170)
Index Cond: (id = stoqs_measuredparameter.measurement_id)
Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))
-> Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint (cost=0.00..5.77 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=20170)
Index Cond: (id = stoqs_measurement.instantpoint_id)
Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone))
-> Hash (cost=18.82..18.82 rows=30 width=336) (actual time=0.151..0.151 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Hash Join (cost=1.09..18.82 rows=30 width=336) (actual time=0.035..0.145 rows=7 loops=1)
Hash Cond: (stoqs_activity.platform_id = stoqs_platform.id)
-> Seq Scan on stoqs_activity (cost=0.00..16.77 rows=177 width=66) (actual time=0.005..0.069 rows=177 loops=1)
-> Hash (cost=1.07..1.07 rows=1 width=278) (actual time=0.014..0.014 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on stoqs_platform (cost=0.00..1.07 rows=1 width=278) (actual time=0.008..0.012 rows=1 loops=1)
Filter: ((name)::text = 'dorado'::text)
Total runtime: 770.445 ms
(42 rows)
We assume that steps taken to improve the worst-case query scenario will also improve these kind of queries. If anything above pops out as needing better planning please let us know that too!
Thanks,
Mike
--
Mike McCann
Software Engineer
Monterey Bay Aquarium Research Institute
7700 Sandholdt Road
Moss Landing, CA 95039-9644
Voice: 831.775.1769 Fax: 831.775.1736 http://www.mbari.org
Another response:
From: Scott Marlowe <scott.marlowe@gmail.com> Subject: Re: [PERFORM] Hardware suggestions for maximum read performance Date: May 13, 2013 6:09:58 PM PDT To: Mike McCann <mccann@mbari.org> Cc: Jeff Janes <jeff.janes@gmail.com>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>, Yuri Levinsky <yuril@celltick.com>, Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net> On Mon, May 13, 2013 at 5:58 PM, Mike McCann <mccann@mbari.org> wrote: > We assume that steps taken to improve the worst-case query scenario will > also improve these kind of queries. If anything above pops out as needing > better planning please let us know that too! Bad assumption. If your real workload will be queries like the one here that takes 700 ms, but you'll be running 10,000 of them a second, you're tuning / hardware choices are going to be much different then if your query is going to be the previous 7 second one. Use realistic queries, not ones that are nothing like what your real ones will be. then use pgbench and its ability to run custom sql scripts to get a REAL idea how your hardware performs. Note that if you will run the slow query you posted like once a minute and roll it up or cache it then don't get too worried about it. Pay attention to the queries that will add up, in aggregate, to your greatest load.
Not a whole lot of opinions except that tuning a server requires diligent work and performance is something one must do some work to achieve, and cannot be had by simply throwing money at the problem.
20 May 2013
A few more responses over the weekend:
From: Greg Smith <greg@2ndQuadrant.com> Subject: Re: [PERFORM] Hardware suggestions for maximum read performance Date: May 19, 2013 7:44:16 PM PDT To: Mike McCann <mccann@mbari.org> Cc: pgsql-performance@postgresql.org, Yuri Levinsky <yuril@celltick.com>, Scott Marlowe <scott.marlowe@gmail.com>, Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net>, Jeff Janes <jeff.janes@gmail.com> On 5/13/13 6:36 PM, Mike McCann wrote: > stoqs_march2013_s=# explain analyze select * from > stoqs_measuredparameter order by datavalue; > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------ > Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual > time=2503.078..2937.130 rows=3381814 loops=1) > Sort Key: datavalue > Sort Method: quicksort Memory: 362509kB > -> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 > rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1) > Total runtime: 3094.601 ms > (5 rows) > I tried changing random_page_cost to from 4 to 1 and saw no change. Have you tried putting an index by datavalue on this table? Once you've done that, then changing random_page_cost will make using that index look less expensive. Sorting chews through a good bit of CPU time, and that's where all of your runtime is being spent at--once you increase work_mem up very high that is. > I'm wondering now what changes might get this query to run in less than > one second. If all the data is in memory, then will faster CPU and > memory be the things that help? You're trying to fix a fundamental design issue with hardware. That usually doesn't go well. Once you get a box big enough to hold the whole database in RAM, beyond that the differences between server systems are relatively small. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
and
From: Scott Marlowe <scott.marlowe@gmail.com> Subject: Re: [PERFORM] Hardware suggestions for maximum read performance Date: May 19, 2013 8:57:32 PM PDT To: Greg Smith <greg@2ndquadrant.com> Cc: Mike McCann <mccann@mbari.org>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>, Yuri Levinsky <yuril@celltick.com>, Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net>, Jeff Janes <jeff.janes@gmail.com> On Sun, May 19, 2013 at 8:44 PM, Greg Smith <greg@2ndquadrant.com> wrote: >On 5/13/13 6:36 PM, Mike McCann wrote: >> >> stoqs_march2013_s=# explain analyze select * from >> stoqs_measuredparameter order by datavalue; >> >> QUERY PLAN >> >>------------------------------------------------------------------------------------------------------------------------------------------ >> Sort (cost=422106.15..430560.68 rows=3381814 width=20) (actual >> time=2503.078..2937.130 rows=3381814 loops=1) >> Sort Key: datavalue >> Sort Method: quicksort Memory: 362509kB >> -> Seq Scan on stoqs_measuredparameter (cost=0.00..55359.14 >> rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1) >> Total runtime: 3094.601 ms >> (5 rows) >> >> I tried changing random_page_cost to from 4 to 1 and saw no change. > > > Have you tried putting an index by datavalue on this table? Once you've > done that, then changing random_page_cost will make using that index look > less expensive. Sorting chews through a good bit of CPU time, and that's > where all of your runtime is being spent at--once you increase work_mem up > very high that is. This++ plus cluster on that index if you can.
2 July 2013
We made the decision made to purchase a server with a FusionIO card. Server is now set up on the network with name "kraken". Here is the confluence page for kraken's configuration settings.
Setting up ability to run pgbench-tools on all the various servers. This may be done per user account by first installing from git and following the instructions in README.rst; however, the scripts need to run commands requiring createdb and vacuum privileges, so it's best to run as user postgres. So, logging into pgtest as user postgres and getting things set up:
-bash-4.1$ pwd /var/lib/pgsql -bash-4.1$ mkdir dev -bash-4.1$ cd dev -bash-4.1$ git clone git://git.postgresql.org/git/pgbench-tools.git Initialized empty Git repository in /var/lib/pgsql/dev/pgbench-tools/.git/ remote: Counting objects: 108, done. remote: Compressing objects: 100% (106/106), done. remote: Total 108 (delta 54), reused 0 (delta 0) Receiving objects: 100% (108/108), 24.61 KiB, done. Resolving deltas: 100% (54/54), done. -bash-4.1$ createdb results -bash-4.1$ createdb pgbench -bash-4.1$ cd pgbench-tools/ -bash-4.1$ psql -f init/resultdb.sql -d results psql:init/resultdb.sql:1: ERROR: table "timing" does not exist CREATE TABLE CREATE INDEX psql:init/resultdb.sql:12: ERROR: table "tests" does not exist psql:init/resultdb.sql:29: NOTICE: CREATE TABLE will create implicit sequence "tests_test_seq" for serial column "tests.test" psql:init/resultdb.sql:29: NOTICE: CREATE TABLE will create implicit sequence "tests_set_seq" for serial column "tests.set" CREATE TABLE psql:init/resultdb.sql:31: ERROR: table "testset" does not exist psql:init/resultdb.sql:36: NOTICE: CREATE TABLE will create implicit sequence "testset_set_seq" for serial column "testset.set" CREATE TABLE INSERT 0 1 psql:init/resultdb.sql:40: ERROR: table "test_bgwriter" does not exist CREATE TABLE -bash-4.1$ psql -c "UPDATE testset SET info='Initial test set' WHERE set=1" -d results UPDATE 1
I think those ERROR messages can be ignored because the following tests work.
The gnuplot package is needed for producing graphs of the results:
sudo -c "yum install gnuplot"
Edit the config file with hard path to pgbench and reduced number of scale and client runs so that we have a quick execution to just test things:
-bash-4.1$ mv config config.130702 -bash-4.1$ cp config.130702 config -bash-4.1$ vi config -bash-4.1$ diff config.130702 config 8c8 < PGBENCHBIN=`which pgbench` --- > PGBENCHBIN=/usr/pgsql-9.1/bin/pgbench 42,44c42,47 < SCALES="1 10 100 1000" < SETCLIENTS="1 2 4 8 16 32" < SETTIMES=3 --- > SCALES="1" > SETCLIENTS="1" > SETTIMES=1 > #SCALES="1 10 100 1000" > #SETCLIENTS="1 2 4 8 16 32" > #SETTIMES=3
Edit scripts to use "local socket" connection (removing "-h $TESTHOST") for the postgres user:
-bash-4.1$ diff runset.130702 runset 19c19 < TESTPSQL="psql -h $TESTHOST -U $TESTUSER -p $TESTPORT -d $TESTDB" --- > TESTPSQL="psql -U $TESTUSER -p $TESTPORT -d $TESTDB" 24c24 < $PGBENCHBIN -i -s $SCALE -h $TESTHOST -U $TESTUSER -p $TESTPORT $TESTDB --- > $PGBENCHBIN -i -s $SCALE -U $TESTUSER -p $TESTPORT $TESTDB -bash-4.1$ diff benchwarmer.130702 benchwarmer 84,85c84,85 < TESTPSQL="psql -h $TESTHOST -U $TESTUSER -p $TESTPORT -d $TESTDB" < RESULTPSQL="psql -h $RESULTHOST -U $RESULTUSER -p $RESULTPORT -d $RESULTDB" --- > TESTPSQL="psql -U $TESTUSER -p $TESTPORT -d $TESTDB" > RESULTPSQL="psql -U $RESULTUSER -p $RESULTPORT -d $RESULTDB" 158c158 < $PGBENCHBIN -f $BASEDIR/$TESTDIR/$SCRIPT -s $SCALE -l -n $TESTLEN -U $TESTUSER -h $TESTHOST -p $TESTPORT -c $CLIENTS $WORKERS $TESTDB > results.txt & --- > $PGBENCHBIN -f $BASEDIR/$TESTDIR/$SCRIPT -s $SCALE -l -n $TESTLEN -U $TESTUSER -p $TESTPORT -c $CLIENTS $WORKERS $TESTDB > results.txt & -bash-4.1$ diff webreport.130702 webreport 10c10 < RESULTPSQL="psql -h $RESULTHOST -U $RESULTUSER -p $RESULTPORT -d $RESULTDB" --- > RESULTPSQL="psql -U $RESULTUSER -p $RESULTPORT -d $RESULTDB"
And now a test run looks like:
-bash-4.1$ ./runset Removing old pgbench tables DROP TABLE VACUUM Creating new pgbench tables NOTICE: table "pgbench_branches" does not exist, skipping NOTICE: table "pgbench_tellers" does not exist, skipping NOTICE: table "pgbench_accounts" does not exist, skipping NOTICE: table "pgbench_history" does not exist, skipping creating tables... 10000 tuples done. 20000 tuples done. 30000 tuples done. 40000 tuples done. 50000 tuples done. 60000 tuples done. 70000 tuples done. 80000 tuples done. 90000 tuples done. 100000 tuples done. set primary key... NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers" NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts" vacuum...done. Run set #1 of 1 with 1 clients scale=1 Running tests using: psql -U postgres -p 5432 -d pgbench Storing results using: psql -U postgres -p 5432 -d results Cleaning up database pgbench TRUNCATE TABLE VACUUM CHECKPOINT INSERT 0 1 This is test 3 Script select.sql executing for 1 concurrent users... UPDATE 1 transaction type: Custom query scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 60 s number of transactions actually processed: 547599 tps = 9126.622772 (including connections establishing) tps = 9127.071214 (excluding connections establishing) Worst latency results: 2018 2311 2370 3155 3476 Could not find/open font when opening font "arial", using internal non-scalable font Could not find/open font when opening font "arial", using internal non-scalable font Could not find/open font when opening font "arial", using internal non-scalable font Warning: empty x range [1:1], adjusting to [0.99:1.01] Warning: empty y range [9228:9228], adjusting to [9135.72:9320.28] Warning: empty y2 range [21:21], adjusting to [20.79:21.21] Could not find/open font when opening font "arial", using internal non-scalable font Warning: empty x range [1:1], adjusting to [0.99:1.01] Warning: empty y range [9228:9228], adjusting to [9135.72:9320.28] Could not find/open font when opening font "arial", using internal non-scalable font Warning: empty x range [1:1], adjusting to [0.99:1.01] Warning: empty y range [1:1], adjusting to [0.99:1.01] Warning: empty z range [9228:9228], adjusting to [9135.72:9320.28]
3 July 2013
Making the results visible on an internally accessible web page. Turn on httpd and make an alias to the results directory that is produced above:
sudo /bin/bash chkconfig httpd on /sbin/service httpd start
Confirm that http://pgtest.shore.mbari.org/ is visible. It wasn't because the firewall on pgtest was preventing it. A call to Pat Allen fixed this. Next, edit /etc/httpd/conf/httpd.conf to add the alias and make the postgres home dir world executable (don't do this on a production system!) so that the apache httpd process can serve the directory:
# diff httpd.conf.130703 httpd.conf 559a560,569 > # Expose our pgbench-toolbox results > Alias /pgbench /var/lib/pgsql/dev/pgbench-tools/results > > <Directory "/var/lib/pgsql/dev/pgbench-tools/results"> > Options Indexes FollowSymLinks > AllowOverride None > Order allow,deny > Allow from all > </Directory> > # chmod +x /var/lib/pgsql
The pgbench-toolbox results can now be shared at http://pgtest.shore.mbari.org/pgbench/
Posted by drolicheck at Jul 03, 2013 16:33
